﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PeixunDAL
    {
        string str = "server = LAPTOP-KE3OLTQM; database = RenLiZiYuanGuanli; uid = sa ; pwd = 123456";
        /// <summary>
        /// 培训记录显示
        /// </summary>
        /// <returns></returns>
        public List<train_tbModel> peixunshow()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand comd = new SqlCommand("peixunshow", coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                comd.CommandType = CommandType.StoredProcedure;
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<train_tbModel> list = new List<train_tbModel>();
                foreach (DataRow item in tb.Rows)
                {
                    train_tbModel t = new train_tbModel();
                    t.train_id = Convert.ToInt32(item["train_id"]);
                    t.train_cost = Convert.ToDecimal(item["train_cost"]);
                    t.principal = item["train_principal"].ToString();
                    t.train_grade = Convert.ToInt32(item["train_grade"]);
                    t.train_hour = item["train_hour"].ToString();
                    t.train_nature = item["train_nature"].ToString();
                    t.train_site = item["train_site"].ToString();
                    t.train_sponsor = item["train_sponsor"].ToString();
                    t.train_subjects = item["train_subjects"].ToString();
                    t.train_teacher = item["train_teacher"].ToString();
                    t.train_times = item["train_times"].ToString();
                    t.train_way = item["train_way"].ToString();
                    t.trainee_id = Convert.ToInt32(item["trainee_id"]);
                    t.traintype_id = Convert.ToInt32(item["traintype_id"]);
                    t.traintype_name = item["traintype_name"].ToString();
                    t.userID = Convert.ToInt32(item["Staff_ID"]);
                    t.userName = item["Staff_Name"].ToString();
                    list.Add(t);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 员工证书显示
        /// </summary>
        /// <returns></returns>
        public List<certificate_tbModel> certificate_tb()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                string sql = "select * from certificate_tb a ,trainee b,Staff c where a.trainee_id=b.trainee_id and b.userID=c.Staff_ID";
                SqlCommand comd = new SqlCommand(sql, coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
               
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<certificate_tbModel> list = new List<certificate_tbModel>();
                foreach (DataRow item in tb.Rows)
                {
                    certificate_tbModel t = new certificate_tbModel();
                    t.certificate_id = Convert.ToInt32(item["certificate_id"]);
                    t.certificate_name = item["certificate_name"].ToString();
                    t.principal = item["train_principal"].ToString();
                    t.train_grade = Convert.ToInt32(item["train_grade"]);
                    t.train_hour = item["train_hour"].ToString();
                    t.train_sponsor = item["train_sponsor"].ToString();
                    t.train_subjects = item["train_subjects"].ToString();
                    t.train_teacher = item["train_teacher"].ToString();
                    t.train_way = item["train_way"].ToString();
                    t.userID = Convert.ToInt32(item["Staff_ID"]);
                    t.userName = item["Staff_Name"].ToString();
                    list.Add(t);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训类型显示
        /// </summary>
        /// <returns></returns>
        public List<traintype_tbModel> traintypeshow()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand comd = new SqlCommand("traintype_tbshow", coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                comd.CommandType = CommandType.StoredProcedure;
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<traintype_tbModel> list = new List<traintype_tbModel>();
                foreach (DataRow item in tb.Rows)
                {
                    traintype_tbModel t = new traintype_tbModel();

                    t.traintype_id = Convert.ToInt32(item["traintype_id"]);
                    t.traintype_name = item["traintype_name"].ToString();

                    list.Add(t);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训类型的维护添加
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int traintypeadd(traintype_tbModel t)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                string sql = "insert into traintype_tb  values('"+t.traintype_name+"')";
                SqlCommand comd = new SqlCommand(sql, coon);
                try
                {
                    return comd.ExecuteNonQuery();
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训类型的反填
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
       public traintype_tbModel traintypeft(int id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                string sql = "select * from traintype_tb where traintype_id=" + id + " ";
                SqlDataAdapter dap = new SqlDataAdapter(sql, coon);
                DataTable dt = new DataTable();
                dap.Fill(dt);
                traintype_tbModel t = new traintype_tbModel();
                t.traintype_id =Convert.ToInt32(dt.Rows[0]["traintype_id"]);
                t.traintype_name = dt.Rows[0]["traintype_name"].ToString();
                try
                {
                    return t;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训类型删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
       public int traintypedel(int id)
       {
           using (SqlConnection coon = new SqlConnection(str))
           {

               if (coon.State == ConnectionState.Closed)
               {
                   coon.Open();
               }
               string sql = "delete  from traintype_tb where traintype_id="+id+" ";
               SqlCommand cmd=new SqlCommand(sql,coon);
               int t=cmd.ExecuteNonQuery();
               try
               {
                   return t;
               }
               catch (Exception)
               {

                   throw;
               }
           }
       }
        /// <summary>
        /// 培训类型的修改
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int traintypeupt(traintype_tbModel t)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                string sql = "update traintype_tb set traintype_name='" + t.traintype_name + "' where traintype_id="+t.traintype_id+"";
                SqlCommand comd = new SqlCommand(sql, coon);
                try
                {
                    return comd.ExecuteNonQuery();
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训人员的添加
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int traineeADD(traineeModel t)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("traineeadd", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter("@userID",SqlDbType.NVarChar,100),
                                        new SqlParameter("@train_sponsor",SqlDbType.VarChar,200),
                                        new SqlParameter("@train_way",SqlDbType.VarChar,50),
                                        new SqlParameter("@train_principal",SqlDbType.VarChar,100),
                                        new SqlParameter("@train_grade",SqlDbType.Int),
                                        new SqlParameter("@train_teacher",SqlDbType.NVarChar,50),
                                        new SqlParameter("@train_hour",SqlDbType.VarChar,50),
                                        new SqlParameter("@train_subjects",SqlDbType.NVarChar,100),
                                        new SqlParameter("@traintype_id",SqlDbType.Int),
                                      };
                pars[0].Value = t.userID;
                pars[1].Value = t.train_sponsor;
                pars[2].Value = t.train_way;
                pars[3].Value = t.principal;
                pars[4].Value = t.train_grade;
                pars[5].Value = t.train_teacher;
                pars[6].Value = t.train_hour;
                pars[7].Value = t.train_subjects;
                pars[8].Value = t.traintype_id;
                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 培训人员表的删除
        /// </summary>
        /// <param name="trainee_id"></param>
        /// <returns></returns>
        public int traineeDEL(int trainee_id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("traineedel", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter("@trainee_id",SqlDbType.Int),
                                     
                                      };
                pars[0].Value = trainee_id;
                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }

        /// <summary>
        /// 培训人员表显示
        /// </summary>
        /// <returns></returns>
        public List<traineeModel> traineeshow()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand comd = new SqlCommand("traineeshow", coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                comd.CommandType = CommandType.StoredProcedure;
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<traineeModel> list = new List<traineeModel>();
                foreach (DataRow item in tb.Rows)
                {
                    traineeModel t = new traineeModel();
                    t.principal = item["train_principal"].ToString();
                    t.train_grade = Convert.ToInt32(item["train_grade"]);
                    t.train_hour = item["train_hour"].ToString();
                    t.train_sponsor = item["train_sponsor"].ToString();
                    t.train_subjects = item["train_subjects"].ToString();
                    t.train_teacher = item["train_teacher"].ToString();
                    t.train_way = item["train_way"].ToString();
                    t.trainee_id = Convert.ToInt32(item["trainee_id"]);
                    t.traintype_id = Convert.ToInt32(item["traintype_id"]);
                    t.traintype_name = item["traintype_name"].ToString();
                    t.userID = Convert.ToInt32(item["Staff_ID"]);
                    t.userName = item["Staff_Name"].ToString();
                    list.Add(t);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训人员表反填
        /// </summary>
        /// <returns></returns>
        public List<traineeModel> traineeft(int trainee_id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand comd = new SqlCommand("traineeft", coon);
              
               
                SqlParameter[] pars = { 
                                        new SqlParameter("@trainee_id",SqlDbType.Int),
                                     
                                      };
                pars[0].Value = trainee_id;
                comd.Parameters.AddRange(pars);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                comd.CommandType = CommandType.StoredProcedure;
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<traineeModel> list = new List<traineeModel>();
                foreach (DataRow item in tb.Rows)
                {
                    traineeModel t = new traineeModel();
                    t.principal = item["train_principal"].ToString();
                    t.train_grade = Convert.ToInt32(item["train_grade"]);
                    t.train_hour = item["train_hour"].ToString();
                    t.train_sponsor = item["train_sponsor"].ToString();
                    t.train_subjects = item["train_subjects"].ToString();
                    t.train_teacher = item["train_teacher"].ToString();
                    t.train_way = item["train_way"].ToString();
                    t.trainee_id = Convert.ToInt32(item["trainee_id"]);
                    t.traintype_id = Convert.ToInt32(item["traintype_id"]);
                    t.traintype_name = item["traintype_name"].ToString();
                    t.userID = Convert.ToInt32(item["Staff_ID"]);
                    t.userName = item["Staff_Name"].ToString();
                    list.Add(t);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        /// <summary>
        /// 培训人员的修改
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int traineeUPT(traineeModel t)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("traineeupt", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter("@trainee_id",SqlDbType.Int),
                                        new SqlParameter("@userID",SqlDbType.Int),
                                        new SqlParameter("@train_sponsor",SqlDbType.VarChar,200),
                                        new SqlParameter("@train_way",SqlDbType.VarChar,50),
                                        new SqlParameter("@train_principal",SqlDbType.VarChar,100),
                                        new SqlParameter("@train_grade",SqlDbType.Int),
                                        new SqlParameter("@train_teacher",SqlDbType.NVarChar,50),
                                        new SqlParameter("@train_hour",SqlDbType.VarChar,50),
                                        new SqlParameter("@train_subjects",SqlDbType.NVarChar,100),
                                        new SqlParameter("@traintype_id",SqlDbType.Int),
                                      };
                pars[0].Value = t.trainee_id;
                pars[1].Value = t.userID;
                pars[2].Value = t.train_sponsor;
                pars[3].Value = t.train_way;
                pars[4].Value = t.principal;
                pars[5].Value = t.train_grade;
                pars[6].Value = t.train_teacher;
                pars[7].Value = t.train_hour;
                pars[8].Value = t.train_subjects;
                pars[9].Value = t.traintype_id;
                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
    
    }
}
